package com.alpha.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.alpha.bom.Answer;
import com.alpha.bom.Question;
import com.alpha.dao.interfaces.IQuestionDao;

public class QuestionDao extends AlphaDao implements IQuestionDao{
	private Connection connection;
	private Statement statement;
	private PreparedStatement preparedStatement;
	private ResultSet resultSet;
	private static Logger logger = Logger.getLogger(QuestionDao.class.getName());
	
	public void connect() throws NamingException, SQLException {
		try {
			Context context= new InitialContext();
			DataSource ds = (DataSource) context.lookup("java:comp/env/jdbc/alpha");			
			connection = ds.getConnection();
		} catch (NamingException e) {
			logger.error(e.getMessage());
			throw e;
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		}
	}
	
	public void disconnect() throws SQLException {
		if(this.resultSet != null) {
			try {
				this.resultSet.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
		if(this.preparedStatement != null) {
			try {
				this.preparedStatement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
		if(this.statement != null) {
			try {
				this.statement.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
		if(this.connection != null) {
			try {
				this.connection.close();
			} catch (SQLException e) {
				logger.error(e.getMessage());
				throw e;
			}
		}
	}
	
	public Question retrieveQuestion(int questionId) throws SQLException, NamingException {
		Question question = null;
		this.connect();
		try {
			String select = "SELECT * FROM tbl_question LEFT JOIN (tbl_category, tbl_question_type) ON tbl_question.question_category_id=tbl_category.category_id AND tbl_question.question_type_id=tbl_question_type.question_type_id WHERE question_id="+questionId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				questionId = this.resultSet.getInt("question_id");
				String questionType = this.resultSet.getString("question_type");
				String questionText = this.resultSet.getString("question_text");
				
				//
				questionText = questionText.replaceAll("\n", "<br>");
				questionText = questionText.replaceAll(" ", "&nbsp");
				//
				
				String category = this.resultSet.getString("category_name");
				
				int correctAnswer = this.resultSet.getInt("correct_answer_id");
				AnswerDao aDao = new AnswerDao();
				Answer answer = new Answer(correctAnswer);
				
				ArrayList<Answer> choices = aDao.retrieveChoices(questionId);
				answer = choices.get(choices.indexOf(answer));
				
				question = new Question(questionId, questionType, questionText, answer, category);
				question.setChoices(choices);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return question;
	}
	
	public int retrieveNextQuestionId() throws NamingException, SQLException {
		int questionId = 0;
		try {
			String select = "SELECT MAX(question_id)+1 FROM tbl_question";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				questionId = this.resultSet.getInt(1);
				questionId = questionId > 0 ? questionId : 1;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} 
		
		return questionId;
	}
	
	public ArrayList<Question> retrieveAllQuestions() throws SQLException, NamingException {
		ArrayList<Question> questions = new ArrayList<Question>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_question LEFT JOIN (tbl_question_type, tbl_category) ON tbl_question.question_type_id=tbl_question_type.question_type_id AND tbl_question.question_category_id=tbl_category.category_id";
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int questionId = this.resultSet.getInt("question_id");
				String questionType = this.resultSet.getString("question_type");
				String questionText = this.resultSet.getString("question_text");
				
				//
				questionText = questionText.replaceAll("\n", "<br>");
				questionText = questionText.replaceAll(" ", "&nbsp");
				//
				
				String category = this.resultSet.getString("category_name");
				
				int correctAnswer = this.resultSet.getInt("correct_answer_id");
				AnswerDao aDao = new AnswerDao();
				Answer answer = new Answer(correctAnswer);
				
				ArrayList<Answer> choices = aDao.retrieveChoices(questionId);
				answer = choices.get(choices.indexOf(answer));
				
				Question question = new Question(questionId, questionType, questionText, answer, category);
				question.setChoices(choices);
				questions.add(question);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return questions;
	}
	
	public ArrayList<Question> retrieveQuestions(int examId) throws SQLException, NamingException {
		ArrayList<Question> questions = new ArrayList<Question>();
		this.connect();
		try {
			String select = "SELECT * FROM tbl_exam_question LEFT JOIN (tbl_question, tbl_question_type, tbl_category) ON tbl_question.question_category_id=tbl_category.category_id AND tbl_question.question_type_id=tbl_question_type.question_type_id AND tbl_exam_question.question_id=tbl_question.question_id WHERE exam_id="+examId;
			this.statement = this.connection.createStatement();
			this.resultSet = this.statement.executeQuery(select);
			while(this.resultSet.next()) {
				int questionId = this.resultSet.getInt("question_id");
				String questionType = this.resultSet.getString("question_type");
				String questionText = this.resultSet.getString("question_text");
				
				//
				questionText = questionText.replaceAll("\n", "<br>");
				questionText = questionText.replaceAll(" ", "&nbsp");
				//
				
				String category = this.resultSet.getString("category_name");
				
				int correctAnswer = this.resultSet.getInt("correct_answer_id");
				AnswerDao aDao = new AnswerDao();
				Answer answer = new Answer(correctAnswer);
				
				ArrayList<Answer> choices = aDao.retrieveChoices(questionId);
				answer = choices.get(choices.indexOf(answer));
				
				Question question = new Question(questionId, questionType, questionText, answer, category);
				question.setChoices(choices);
				questions.add(question);
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return questions;
	}
	
	public boolean insertQuestion(Question question) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_question(question_id, question_text, question_type_id, question_category_id) VALUES(?,?,?,?)";
		try {
			question.setQuestionId(this.retrieveNextQuestionId());
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, question.getQuestionId());
			this.preparedStatement.setString(2, question.getQuestion());
			this.preparedStatement.setInt(3, question.getQuestionType().equals("MultipleChoice") ? 1 : 2);
			CategoryDao cDao = new CategoryDao();
			this.preparedStatement.setInt(4, cDao.retrieveCategoryId(question.getCategory()));
			
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
			
			AnswerDao aDao = new AnswerDao();
			for(Answer answer : question.getChoices()) {
				aDao.insertAnswer(answer, question.getQuestionId());
			}
			
			this.updateQuestion(question);
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		
		return success;
	}
	
	public boolean insertQuestionToExam(int questionId, int examId) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String insert = "INSERT INTO tbl_exam_question(exam_id, question_id) VALUES(?,?)";
		try {
			this.preparedStatement = this.connection.prepareStatement(insert);
			this.preparedStatement.setInt(1, examId);
			this.preparedStatement.setInt(2, questionId);
			
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
		}
		
		return success;
	}
	
	public boolean removeQuestionFromExam(int questionId, int examId) throws NamingException, SQLException {
		boolean success = false;
		this.connect();
		String delete = "DELETE FROM tbl_exam_question WHERE question_id=? AND exam_id=?";
		try {
			this.preparedStatement = this.connection.prepareStatement(delete);
			this.preparedStatement.setInt(1, questionId);
			this.preparedStatement.setInt(1, examId);
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} finally {
			this.disconnect();
		}
		
		return success;
	}
	
	public boolean updateQuestion(Question question) throws NamingException, SQLException {
		boolean success = false;
		String update = "UPDATE tbl_question SET correct_answer_id=? WHERE question_id=?";
		try {
			this.preparedStatement = this.connection.prepareStatement(update);
			
			this.preparedStatement.setInt(1, question.getCorrectAnswer().getAnswerId());
			this.preparedStatement.setInt(2, question.getQuestionId());
			int rows = this.preparedStatement.executeUpdate();
			if(rows > 0) {
				success = true;
			}
		} catch (SQLException e) {
			logger.error(e.getMessage());
			throw e;
		} 
		
		return success;
	}
}
